Introduction to Structured Query Language (SQL)

Course: MySQL Training

Topic: Introduction to Structured Query Language (SQL)

Course Link: JustAcademy MySQL Training Course

Demo Registration: Register for MySQL Course Demo

1. Introduction to SQL

Structured Query Language (SQL) is a standard language used to communicate with relational database management systems (RDBMS). It allows users to create databases and tables, insert records, retrieve information, update existing data, delete records, and manage database structures.

SQL is widely used in database development, data analysis, web application development, reporting, business intelligence, and database administration. MySQL is one of the relational database management systems that uses SQL to work with structured data.

For example, an educational institute can use SQL to store student details, course information, attendance records, examination results, and fee payments. SQL commands help users add new students, find students enrolled in a particular course, update contact details, and generate reports.

Example of SQL

SELECT * FROM students;

This query retrieves all columns and rows from a table named students, provided the table exists and the connected account has the required permissions.

2. Learning Objectives

• Understand the meaning and purpose of SQL.
• Learn the relationship between SQL, MySQL, and relational databases.
• Understand databases, tables, rows, and columns.
• Identify the major categories of SQL commands.
• Learn basic SQL syntax and statement structure.
• Understand how to create databases and tables.
• Learn how to insert, retrieve, update, and delete records.
• Understand filtering, sorting, and grouping data.
• Explore SQL constraints, joins, and aggregate functions.
• Practice writing SQL queries in MySQL.

3. What Is Structured Query Language?

Structured Query Language, commonly called SQL, is a language designed for working with relational databases. It provides statements for defining database structures, manipulating records, retrieving information, controlling access, and managing transactions.

SQL is declarative in nature. In many queries, users specify what information they want rather than writing every step required to retrieve it. The database management system determines an execution strategy for processing the query.

Example

SELECT student_name
FROM students
WHERE course = 'MySQL';

This query requests the names of students enrolled in the MySQL course. The database evaluates the condition and returns the matching records.

Key Characteristics of SQL

• Standardized language: SQL is based on international standards, although individual database systems provide different features and syntax extensions.
• Declarative: Queries generally specify the required result rather than the complete execution procedure.
• Data manipulation: SQL supports inserting, updating, deleting, and retrieving records.
• Structure definition: SQL can create and modify databases, tables, views, and other database objects.
• Security management: Supported statements allow administrators to manage accounts and permissions.
• Transaction support: SQL provides statements for controlling transactions in supported database engines.
• Reporting: SQL can filter, sort, group, and summarize information.

4. What Is MySQL?

MySQL is a relational database management system that stores and manages data in tables. It uses SQL statements to create database structures and perform operations on stored information.

MySQL is commonly used in websites, business applications, content management systems, e-commerce platforms, and data-driven software solutions.

SQL vs. MySQL

5. Understanding Relational Databases

A relational database organizes related information into tables. Tables contain rows and columns, and relationships between tables can be established using keys.

Important Database Terms

Example Student Table

In this table, each row represents one student, while each column stores a particular attribute of that student.

6. How SQL Works

SQL enables users and applications to send requests to a database server. The server checks the statement, verifies permissions, determines how to execute it, accesses or modifies data, and returns the result or an error.

Basic SQL Workflow

User or Application
        |
        v
Write an SQL Statement
        |
        v
Send Statement to MySQL Server
        |
        v
Parse and Validate the Statement
        |
        v
Check Permissions and Execute
        |
        v
Read or Modify Database Data
        |
        v
Return Results or Status

Example

SELECT student_name
FROM students
WHERE age > 20;

MySQL processes the query and returns the names of students whose age is greater than 20, assuming the table contains the expected columns.

7. Categories of SQL Commands

SQL statements are commonly grouped according to their purpose. These categories are useful for learning SQL and understanding database operations.

Note: This is a common educational classification. Different textbooks may classify SELECT and some other statements differently. Exact transaction behavior depends on the statement, storage engine, and MySQL configuration.

8. Data Definition Language (DDL)

DDL statements define and modify database objects such as databases, tables, indexes, and views.

8.1 CREATE

The CREATE statement creates a database object.

CREATE DATABASE school_db;

Create a table inside the selected database:

USE school_db;
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    age INT,
    course VARCHAR(100)
);

8.2 ALTER

The ALTER statement changes the structure of an existing database object.

ALTER TABLE students
ADD email VARCHAR(150);

This example adds an email column to the students table.

8.3 DROP

The DROP statement removes a database object.

DROP TABLE students;

Warning: Dropping a table removes its structure and data. Verify the target before executing this statement.

8.4 TRUNCATE

The TRUNCATE TABLE statement removes all rows from a table while retaining the table structure.

TRUNCATE TABLE students;

In MySQL, TRUNCATE has important transaction and foreign-key restrictions and is not equivalent to deleting rows one at a time.

9. Data Manipulation Language (DML)

DML statements are used to add, modify, and remove data stored in database tables.

9.1 INSERT

The INSERT statement adds new records.

INSERT INTO students (student_name, age, course)
VALUES ('Amit', 21, 'MySQL');

9.2 UPDATE

The UPDATE statement modifies existing records.

UPDATE students
SET course = 'Advanced MySQL'
WHERE student_id = 1;

9.3 DELETE

The DELETE statement removes selected records.

DELETE FROM students
WHERE student_id = 1;

Important: Always check the WHERE condition before using UPDATE or DELETE. Omitting the condition can cause the statement to modify or remove every row in the table.

10. Data Query Language (DQL)

The SELECT statement retrieves data from one or more tables. It is one of the most frequently used SQL statements.

Retrieve All Columns

SELECT * FROM students;

Retrieve Specific Columns

SELECT student_name, course
FROM students;

Filter Records with WHERE

SELECT *
FROM students
WHERE age > 20;

Sort Records with ORDER BY

SELECT *
FROM students
ORDER BY student_name ASC;

Limit the Number of Results

SELECT *
FROM students
LIMIT 5;

The LIMIT clause restricts the number of returned rows. Without an explicit ordering, the returned rows should not be assumed to follow a particular order.

11. Data Control Language (DCL)

DCL is commonly used to describe statements that manage database access and privileges.

GRANT

The GRANT statement assigns privileges to a database account or role.

GRANT SELECT
ON school_db.students
TO 'report_user'@'localhost';

This example grants the SELECT privilege on the specified table to an existing account. The account must be configured appropriately, and the person executing the statement must have the required administrative privileges.

REVOKE

The REVOKE statement removes specified privileges.

REVOKE SELECT
ON school_db.students
FROM 'report_user'@'localhost';

Always follow the principle of least privilege: users should receive only the access needed for their responsibilities.

12. Transaction Control Language (TCL)

Transactions group database operations into a unit of work. For transactional storage engines such as InnoDB, transaction-control statements can help ensure that related changes are completed together or rolled back when appropriate.

START TRANSACTION

START TRANSACTION;

This statement begins a transaction.

COMMIT

COMMIT;

COMMIT makes the changes in the current transaction permanent.

ROLLBACK

ROLLBACK;

ROLLBACK cancels uncommitted transactional changes where rollback is supported.

Example

START TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
COMMIT;

This example illustrates transferring money between two accounts. A real financial transaction should also validate account existence, sufficient funds, correct amounts, and other business rules before committing.

13. SQL Syntax and Basic Rules

SQL syntax defines how statements must be written so that the database server can interpret them correctly.

• SQL statements commonly end with a semicolon (;).
• Keywords such as SELECT, FROM, WHERE, and CREATE are conventionally written in uppercase for readability.
• String values are generally enclosed in single quotes.
• Numeric values are generally written without quotation marks.
• Column and table names must match the database structure.
• Comments can explain the purpose of a statement.
• SQL keywords are generally case-insensitive in MySQL, but identifier and string comparison behavior can vary by platform and collation.

Example

SELECT student_name, age
FROM students
WHERE age >= 18
ORDER BY age DESC;

This statement retrieves student names and ages for students aged 18 or above, sorting the results from highest age to lowest age.

SQL Comments

-- This is a single-line comment
SELECT * FROM students;

/* This is a
   multi-line comment */
SELECT student_name FROM students;

14. SQL Data Types

Data types determine what kind of values a table column can store. Choosing appropriate data types helps maintain data consistency and use storage efficiently.

15. SQL Operators

Operators are used to compare values, perform calculations, and combine conditions in SQL expressions.

15.1 Arithmetic Operators

15.2 Comparison Operators

15.3 Logical Operators

SELECT *
FROM students
WHERE age >= 18 AND course = 'MySQL';

SELECT *
FROM students
WHERE course = 'MySQL' OR course = 'SQL';

SELECT *
FROM students
WHERE NOT course = 'Python';

AND requires both conditions to be true, OR requires at least one condition to be true, and NOT negates a condition. SQL uses three-valued logic for NULL comparisons, so NULL values require special handling.

16. Filtering Data with WHERE

The WHERE clause filters rows according to a condition. It is commonly used with SELECT, UPDATE, and DELETE.

Using BETWEEN

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25;

Using IN

SELECT *
FROM students
WHERE course IN ('SQL', 'MySQL');

Using LIKE

SELECT *
FROM students
WHERE student_name LIKE 'A%';

The percent sign (%) matches zero or more characters. The underscore (_) matches a single character.

Checking NULL Values

SELECT *
FROM students
WHERE email IS NULL;

Use IS NULL or IS NOT NULL to test for NULL values. Comparing a value to NULL using = NULL does not work as an ordinary equality check.

17. Sorting and Grouping Data

ORDER BY

The ORDER BY clause sorts the query results in ascending or descending order.

SELECT student_name, age
FROM students
ORDER BY age DESC;

GROUP BY

The GROUP BY clause groups rows that share the same values in selected columns. It is frequently used with aggregate functions.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course;

HAVING

The HAVING clause filters groups after grouping and aggregation.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) >= 2;

WHERE vs. HAVING

18. Aggregate Functions in SQL

Aggregate functions calculate a single result from multiple rows or groups of rows.

Examples

SELECT COUNT(*) AS total_students
FROM students;

SELECT AVG(age) AS average_age
FROM students;

SELECT MIN(age) AS youngest_student,
       MAX(age) AS oldest_student
FROM students;

19. SQL Constraints

Constraints enforce rules on data stored in database tables. They help maintain consistency and prevent invalid records.

20. Introduction to SQL Joins

Joins combine related data from two or more tables using a join condition. They are useful when information is stored in separate but related tables.

Example Tables

Assume a students table contains student_id, student_name, and course_id, while a courses table contains course_id and course_name.

INNER JOIN

Returns rows that match the join condition in both tables.

SELECT s.student_name, c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.course_id;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table. When no right-side row matches, right-side columns are returned as NULL.

SELECT s.student_name, c.course_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.course_id;

RIGHT JOIN

Returns all rows from the right table and matching rows from the left table.

SELECT s.student_name, c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.course_id;

Join Types Summary

21. NULL Values in SQL

NULL represents missing, unknown, or unavailable information. It is different from zero, an empty string, or a regular text value.

Example

SELECT *
FROM students
WHERE email IS NULL;

This query returns students whose email field has no stored value represented by NULL.

Important Points

• Use IS NULL to identify NULL values.
• Use IS NOT NULL to identify values that are not NULL.
• Aggregate functions such as AVG and SUM generally ignore NULL values in the expression.
• COUNT(*) counts rows, including rows with NULL values in some columns.
• Use suitable constraints when a value is required.

22. SQL Views

A view is a named database object defined by a SELECT statement. It can present selected columns and rows from one or more underlying tables.

Create a View

CREATE VIEW mysql_students AS
SELECT student_id, student_name, course
FROM students
WHERE course = 'MySQL';

Query a View

SELECT * FROM mysql_students;

Views can simplify complex queries and present a consistent interface to users. Whether a view can be updated depends on its definition and MySQL's view-updatability rules.

23. SQL Subqueries

A subquery is a query nested inside another SQL statement. It can be used to filter records, calculate values, or provide data for another query.

Example

SELECT student_name, age
FROM students
WHERE age > (
    SELECT AVG(age)
    FROM students
);

This query retrieves students whose age is greater than the average age of the students in the table.

24. SQL Transactions and Data Integrity

Transactions help maintain consistency when multiple related changes must be performed together. With transactional tables such as InnoDB, users can commit a successful transaction or roll back uncommitted changes.

Example

START TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;

For a real transfer, validate both accounts and the business rules before committing. Also note that not all SQL statements are transactional in the same way; some statements cause implicit commits.

25. Practical Project: Student Management Database

Project Objective: Create a basic student management database using SQL and perform common database operations.

Step 1: Create a Database

CREATE DATABASE student_management;

Step 2: Select the Database

USE student_management;

Step 3: Create the Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(150) UNIQUE,
    course VARCHAR(100),
    fees DECIMAL(10,2)
);

Step 4: Insert Sample Data

INSERT INTO students
(student_name, age, email, course, fees)
VALUES
('Amit Sharma', 21, '[email protected]', 'MySQL', 12000.00),
('Priya Verma', 22, '[email protected]', 'SQL', 10000.00),
('Rahul Singh', 20, '[email protected]', 'MySQL', 12000.00),
('Neha Patel', 23, '[email protected]', 'Python', 15000.00),
('Karan Mehta', 19, '[email protected]', 'SQL', 10000.00);

Step 5: Display All Students

SELECT * FROM students;

Step 6: Find Students Enrolled in MySQL

SELECT student_name, email
FROM students
WHERE course = 'MySQL';

Step 7: Sort Students by Age

SELECT student_name, age
FROM students
ORDER BY age ASC;

Step 8: Calculate Total Fees

SELECT SUM(fees) AS total_fees
FROM students;

Step 9: Count Students in Each Course

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course;

Step 10: Update a Student's Course

UPDATE students
SET course = 'Advanced MySQL'
WHERE student_id = 1;

Step 11: Verify the Update

SELECT *
FROM students
WHERE student_id = 1;

Step 12: Delete a Test Record

DELETE FROM students
WHERE student_id = 5;

Run the delete operation only if you intend to remove that record. In real projects, confirm the target record and take appropriate backups or use a transaction when suitable.

Project Skills Practiced

• Database and table creation.
• Data insertion and retrieval.
• Filtering and sorting.
• Aggregate functions and grouping.
• Updating and deleting records.
• Using primary keys and unique constraints.
• Understanding basic database integrity.

26. Common SQL Errors and Best Practices

SQL Best Practices

• Use clear and meaningful database, table, and column names.
• Format queries consistently to improve readability.
• Specify column names in INSERT statements instead of relying on implicit column order.
• Use WHERE clauses carefully when updating or deleting records.
• Use appropriate data types and constraints.
• Use parameterized queries in application code to help prevent SQL injection.
• Grant users only the privileges they need.
• Test queries on sample data before running them against production databases.
• Back up important data and understand transaction behavior.
• Use EXPLAIN and suitable indexes when investigating query performance.

27. Frequently Asked Questions

Q1. What is SQL?

SQL stands for Structured Query Language. It is used to define, retrieve, manipulate, and manage data in relational database systems.

Q2. Is SQL a programming language?

SQL is a database query language. It is declarative in nature, although database systems such as MySQL also support procedural extensions through stored programs.

Q3. What is the difference between SQL and MySQL?

SQL is the language used to communicate with relational databases, while MySQL is a database management system that processes SQL statements.

Q4. What is a database table?

A table stores related records in rows and columns.

Q5. What is the purpose of SELECT?

SELECT retrieves information from tables, views, or expressions.

Q6. What is the difference between DELETE and DROP?

DELETE removes rows from a table, while DROP removes a database object such as a table itself.

Q7. What is a primary key?

A primary key uniquely identifies each row in a table and cannot contain NULL values.

Q8. What is a foreign key?

A foreign key enforces a relationship between values in a table and a referenced key in another table or the same table.

Q9. What is the purpose of WHERE?

WHERE filters rows according to a specified condition.

Q10. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after aggregation.

Q11. What is a JOIN?

A JOIN combines related rows from two or more tables using a join condition.

Q12. What is NULL in SQL?

NULL represents missing or unknown information and must be checked using IS NULL or IS NOT NULL.

Q13. What does GROUP BY do?

GROUP BY groups rows with matching values so aggregate calculations can be performed for each group.

Q14. What is a SQL transaction?

A transaction is a unit of database work that can be committed or, where supported, rolled back.

Q15. Why are SQL constraints important?

Constraints enforce data rules and help maintain the accuracy and integrity of database records.

28. Practice Questions

1. Define Structured Query Language (SQL).
2. Explain the difference between SQL and MySQL.
3. What are databases, tables, rows, and columns?
4. Explain the common categories of SQL commands with examples.
5. Write an SQL statement to create a database named college_db.
6. Write a query to retrieve all records from a table named employees.
7. Write an UPDATE statement to change an employee's department.
8. Explain the difference between DELETE, DROP, and TRUNCATE.
9. What is the difference between a primary key and a foreign key?
10. Write a query that sorts employees by salary in descending order.
11. Explain the purpose of COUNT(), SUM(), AVG(), MIN(), and MAX().
12. What is the difference between WHERE and HAVING?
13. Explain INNER JOIN and LEFT JOIN with examples.
14. What are NULL values, and how can they be checked?
15. Why should parameterized queries be used in application development?

29. Conclusion

Structured Query Language is a fundamental skill for anyone working with relational databases. It provides the commands required to create database structures, insert and retrieve information, modify records, manage permissions, and maintain data integrity.

By learning SQL syntax, command categories, filtering, sorting, grouping, aggregate functions, joins, constraints, and transactions, beginners can develop a strong foundation for working with MySQL and other relational database systems. Regular practice with real datasets helps build confidence and prepares learners for database development, data analytics, and backend application projects.

Explore the Course: JustAcademy MySQL Training Course

Register for a Demo: Register for MySQL Course Demo

Additional Learning Resource: MySQL Official Getting Started Guide